package com.wiscamp.ninechapters.challenges.infrastructure.mappers;

import com.wiscamp.ninechapters.challenges.infrastructure.po.UserGroupPO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.Collection;

@Repository
public interface ChallengeUserGroupMapper extends JpaRepository<UserGroupPO, Long>, JpaSpecificationExecutor<UserGroupPO> {

    /**
     * 通过组队创建人获取组队
     *
     * @param managerId
     * @return
     */

    @Query(value = "select e from UserGroupPO e where e.managerId = :managerId and e.deleted = false")
    UserGroupPO getByManagerId(long managerId);

    /**
     * 按团队积分排名分页获取前100名组队列表
     *
     * @param pageable
     * @return
     */

    @Query(value = "select e from UserGroupPO e where e.totalPoints > 0 order by e.totalPoints desc")
    Page<UserGroupPO> findTop100OrderByTotalPoints(Pageable pageable);

    @Modifying
    @Transactional
    @Query(nativeQuery = true, value = "update user_group e set e.total_rank = 999 where e.total_rank <= 100 and e.group_id not in (:groupIds)")
    void rankingAfter100ByPoints(Collection<Long> groupIds);
}